package com.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shop.dao.IShop;
import com.shop.db.DBUtil;
import com.shop.model.Shop;

public class ShopDao implements IShop{

	/**
	 * 插入数据
	 */
	@Override
	public Integer insert(Shop s) {
		Connection conn = DBUtil.getConn();
		PreparedStatement ptmt = null;
		String sql = "INSERT INTO shop (shopName,price,number,sum,account) VALUES(?,?,?,?,?)";
		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, s.getShopName());
			ptmt.setDouble(2, s.getPrice());
			ptmt.setInt(3, s.getNumber());
			ptmt.setDouble(4, s.getSum());
			ptmt.setString(5, s.getAccount());

			int i = ptmt.executeUpdate();
			return i;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (ptmt != null) {
				try {
					ptmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

		}
		return null;
	}
	/**
	 * 条件查询购物车
	 * @param shopName 
	 * @param account 
	 * @return
	 */
	public List<Shop> selShopName(String shopName, String account) {
		List<Shop> list = new ArrayList<>();
		PreparedStatement ptmt = null;
		ResultSet rs = null;
		//连接数据库
		Connection conn = DBUtil.getConn();
		String sql  = "SELECT*FROM shop where shopName = ? and account = ?";
		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, shopName);
			ptmt.setString(2, account);
			rs = ptmt.executeQuery();
			
			while (rs.next()) {
				Shop shop = new Shop(rs.getInt("id"), rs.getString("shopName"), rs.getDouble("price"), rs.getInt("number"),rs.getDouble("sum"));
				list.add(shop);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}			
			if (ptmt != null) {
				try {
					ptmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
	/**
	 * 查询购物车
	 */
	@Override
	public List<Shop> selShoppingCar(String account) {
		//
		List<Shop> list = new ArrayList<>();
		PreparedStatement ptmt = null;
		ResultSet rs = null;
		//连接数据库
		Connection conn = DBUtil.getConn();
		String sql  = "SELECT id,shopName,price,number,sum FROM shop where account = ?";
		try {
			ptmt = conn.prepareStatement(sql);	
			ptmt.setString(1, account);
			rs = ptmt.executeQuery();
			
			while (rs.next()) {
				Shop shop = new Shop(rs.getInt("id"), rs.getString("shopName"), rs.getDouble("price"), rs.getInt("number"), rs.getDouble("sum"));
				list.add(shop);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}			
			if (ptmt != null) {
				try {
					ptmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}


	@Override
	public Integer delete(String shopName, String account) {
		// 1.获取连接对象
		Connection conn = DBUtil.getConn();
		PreparedStatement ptmt = null;
		String sql = "delete from shop where shopName = ? and account = ?";
		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, shopName);
			ptmt.setString(2, account);
			int i = ptmt.executeUpdate();
			return i;			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (ptmt != null) {
				try {
					ptmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	@Override
	public Integer update(Shop shop) {
		// 1.获取连接对象
		Connection conn = DBUtil.getConn();
		PreparedStatement ptmt = null;
		String sql = "update shop set number = ? , sum = ? where shopName = ? and account = ?";
		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setInt(1, shop.getNumber());
			ptmt.setDouble(2, shop.getSum());
			ptmt.setString(3, shop.getShopName());
			ptmt.setString(4, shop.getAccount());
			int i = ptmt.executeUpdate();
			return i;			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (ptmt != null) {
				try {
					ptmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	
	@Override
	public Integer delShop(String account) {
		// 1.获取连接对象
		Connection conn = DBUtil.getConn();
		PreparedStatement ptmt = null;
		String sql = "delete from shop where account = ?";
		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, account);
			int i = ptmt.executeUpdate();
			return i;			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (ptmt != null) {
				try {
					ptmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	

}
